package com.pineapple.module.core.planting.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.struts2.ServletActionContext;

import com.pineapple.module.core.exception.DuplicateException;
import com.pineapple.module.core.planting.domain.Planting;
import com.pineapple.module.core.planting.domain.PlantingCriteria;
import com.pineapple.module.core.planting.domain.PlantingSearch;

public class PlantingDAO {
	protected int count(PlantingCriteria criteria,Connection conn) throws Exception{
		int totRecord =0;


		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT COUNT(*) AS TOT	 ");
		sql.append(" FROM planting p INNER JOIN area a ON p.area_id = a.area_id INNER JOIN pineapple pi ON p.pineapple_id = pi.pineapple_id  ");
		sql.append("WHERE 1=1 ");

		if(criteria.getPlantingId() !=null && !criteria.getPlantingId().equals("")){
			sql.append("AND p.planting_id='"+criteria.getPlantingId()+"' ");
		}
		if(criteria.getPineappleId()!=null && !criteria.getPineappleId().equals("")){
			sql.append("AND p.pineapple_id='"+criteria.getPineappleId()+"' ");
		}
		if(criteria.getAreaId()!=null && !criteria.getAreaId().equals("")){
			sql.append("AND p.area_id='"+criteria.getAreaId()+"' ");
		}
		if(criteria.getPlantingDateFrom()!=null && !criteria.getPlantingDateFrom().equals("")){
			sql.append("AND planting_date  >='"+dateForInsert(criteria.getPlantingDateFrom())+"' ");
		}
		if(criteria.getPlantingDateTo()!=null && !criteria.getPlantingDateTo().equals("")){
			sql.append("AND planting_date <='"+dateForInsert(criteria.getPlantingDateTo())+"' ");
		}
		if(criteria.getPlantingStatus()!=null && !criteria.getPlantingStatus().equals("")){
			sql.append("AND planting_status ='"+criteria.getPlantingStatus()+"' ");
		}
		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			if (rst.next()) {
				totRecord = rst.getInt("TOT");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return totRecord;
	}

	protected List<PlantingSearch> search(PlantingCriteria criteria,Connection conn) throws Exception{
		List<PlantingSearch> lstPlanting = new ArrayList<PlantingSearch>();
		PlantingSearch planting = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT planting_id,pineapple_name,area_name,area_size,planting_date,planting_status,pineapple_number	 ");
		sql.append(" FROM planting p INNER JOIN area a ON p.area_id = a.area_id INNER JOIN pineapple pi ON p.pineapple_id = pi.pineapple_id  ");
		sql.append("WHERE 1=1 ");

		if(criteria.getPlantingId() !=null && !criteria.getPlantingId().equals("")){
			sql.append("AND p.planting_id='"+criteria.getPlantingId()+"' ");
		}
		if(criteria.getPineappleId()!=null && !criteria.getPineappleId().equals("")){
			sql.append("AND p.pineapple_id='"+criteria.getPineappleId()+"' ");
		}
		if(criteria.getAreaId()!=null && !criteria.getAreaId().equals("")){
			sql.append("AND p.area_id='"+criteria.getAreaId()+"' ");
		}
		if(criteria.getPlantingDateFrom()!=null && !criteria.getPlantingDateFrom().equals("")){
			sql.append("AND planting_date  >='"+dateForInsert(criteria.getPlantingDateFrom())+"' ");
		}
		if(criteria.getPlantingDateTo()!=null && !criteria.getPlantingDateTo().equals("")){
			sql.append("AND planting_date <='"+dateForInsert(criteria.getPlantingDateTo())+"' ");
		}
		if(criteria.getPlantingStatus()!=null && !criteria.getPlantingStatus().equals("")){
			sql.append("AND planting_status ='"+criteria.getPlantingStatus()+"' ");
		}
		sql.append("LIMIT "+(criteria.getStart()-1)+","+((criteria.getStart()-1) + criteria.getLinePerPage()));
		System.out.println(sql.toString());

		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	planting= new PlantingSearch();
		        	planting.setPlantingId(rs.getString("planting_id"));
		        	planting.setPineappleName(rs.getString("pineapple_name"));
		        	planting.setAreaName(rs.getString("area_name"));
		        	planting.setAreaSize(rs.getString("area_size"));
		        	planting.setPlantingDate(dateForShow(rs.getDate("planting_date")));
		        	planting.setPineappleNumber(rs.getString("pineapple_number"));
		        	planting.setPlantingStatus(rs.getString("planting_status"));

		        	lstPlanting.add(planting);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstPlanting;
	}

	protected Map<String,String> pineappleCombo(Connection conn)throws Exception{
		Map<String,String> lstPineapple = new HashMap<String, String>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM pineapple	 ");
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	lstPineapple.put(rs.getString("pineapple_id"),rs.getString("pineapple_name"));
		        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstPineapple;
	}

	protected Map<String,String> areaCombo(Connection conn)throws Exception{
		Map<String,String> lstArea = new HashMap<String, String>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM area a ");
		//sql.append("WHERE a.area_status  IS NULL ");
		try(Statement stmt = conn.createStatement()){
			ResultSet rs = stmt.executeQuery(sql.toString());
			while(rs.next()) {
				lstArea.put(rs.getString("Area_id"),rs.getString("Area_name"));
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstArea;
	}



	protected void add(Connection conn, Planting planting)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO planting (area_id,pineapple_id,planting_date,planting_status,pineapple_number ) ");
		sql.append("VALUES (");


		sql.append("'"+planting.getAreaId()+"' ");
			sql.append(",'"+planting.getPineappleId()+"' ");
			sql.append(",'"+dateForInsert(planting.getPlantingDate())+"' ");
			sql.append(",'1' ");
			sql.append(",'"+planting.getPineappleNumber()+"' ");
			sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	/*protected void update(Connection conn, Planting planting)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE area SET area_status = '2'  WHERE area_id="+planting.getAreaId());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}*/


	protected void edit(Connection conn, Planting planting)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE planting SET ");
		sql.append(" area_id='"+planting.getAreaId()+"' ");
		sql.append(",pineapple_id='"+planting.getPineappleId()+"' ");
		sql.append(",pineapple_number='"+planting.getPineappleNumber()+"' ");
		sql.append(",planting_date='"+dateForInsert(planting.getPlantingDate())+"' ");
		sql.append(",planting_status='"+planting.getPlantingStatus()+"' ");
		sql.append(" WHERE planting_id="+planting.getPlantingId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String plantingIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM planting  ");

		sql.append(" WHERE planting_id IN ("+plantingIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected Planting searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT planting_id,area_id,pineapple_id,planting_date,planting_status,pineapple_number FROM  planting ");
		sql.append("WHERE planting_id ="+id);
		Planting planting = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   planting = new Planting();
		        if(rs.next()) {
		        	planting.setPlantingId(rs.getString("planting_id"));
		        	planting.setAreaId(rs.getString("area_id"));
		        	planting.setPineappleId(rs.getString("pineapple_id"));
		        	planting.setPlantingDate(dateForShow(rs.getDate("planting_date")));
		        	planting.setPlantingStatus(rs.getString("planting_status"));
		        	planting.setPineappleNumber(rs.getString("pineapple_number"));

		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return planting;
	}


	protected void updateStatus(Connection conn,String plantingId,String status)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE  planting SET ");
		sql.append("planting_status='"+status+"' ");
		sql.append(" WHERE planting_id='"+plantingId+"' ");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}

	}

	protected void checkDup(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(planting_id) AS TOT  FROM  planting ");
		sql.append("WHERE area_id ="+id);
		sql.append(" AND planting_status ='1' ");
		try(Statement stmt = conn.createStatement()){
			  ResultSet rst = stmt.executeQuery(sql.toString());
		        	int totRecord = 0;
					if (rst.next()) {
						totRecord = rst.getInt("TOT");
					}

					if (totRecord != 0) {
						ServletActionContext.getRequest().setAttribute("message","�����ū��  �������ö�ѹ�֡�������� ");
						throw new DuplicateException();
					}
		}catch(DuplicateException e){
			throw new DuplicateException();
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	public static String dateForShow(Date date){
		String returnValue = null;
		try {
			if(date != null){

				returnValue = convertToString(date,"dd/MM/yyyy");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}

	public static String convertToString(Date date, String format) {
		if (date != null) {
			SimpleDateFormat formatDate = new SimpleDateFormat(format.toString(),Locale.ENGLISH);

			StringBuilder fdate = new StringBuilder(formatDate.format(date));
			return fdate.toString();
		} else {
			return "";
		}
	}


	public static String dateForInsert(String date){
		String returnValue = null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("en", "EN"));
		try {
			if(date != null && !date.trim().equals("")){
				returnValue = convertToString(sdf.parse(date),"yyyy-MM-dd");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
}
